<?php
/**
 * Search Engine Main Page
 *
 * @author Xavier VILAIN
 *
 */

use Fox\Authorization;
use Doctrine\DBAL\Types\Type;
use Fox\Common\Localization\SubRegion;
use Doctrine\DBAL\Connection;
use Fox\Common\Localization;
use Hive\Ressource\Wish;
use Fox\Query;
use Fox\Behaviors;
use Fox\Query\Messages;
use Doctrine\DBAL\Schema\Index;
use Hive\Ressource;
use Fox\Html\DropList\Confirm;
use Fox\Html\Button;
use Fox\Html\Interval;
use Fox\Html\Input;
use Fox\Html\Screen;
use Fox\Dao\SqlFragment;
use Fox\Generator;
use Fox\Html\DropList;
use Fox\Html\Anchor;
use Fox\SearchEngine;
// Init
$xview = Xview::instance();
$ajax = Xview::instance()->val("ajax",false);
$forced = Xview::instance()->val("forced",false);
$queryFields = Xview::instance()->val("queryFields",false);

$output = null;
// HTML Components
$screen = new Screen("sSearchRessource","vlook");
$screen->mainTitle(Msg(Hive::BUNDLE,Hive::TITLE_CONDIDAT_SEARCH));
//$screen->toolbar()->add(new Fox\Html\Button("add","?mod=ressource&act=add",Msg(Hive::BUNDLE,12)));
$wrapper = $xview->component('ressource', 'criteria');
$screen->add($wrapper);

if(!empty($queryFields)) {
	$data = Query::fieldsToArray($queryFields);
	$forced = true;
	$screen->initialize($data);
}
$screen->add(new Button('savequery','?',Msg(Messages::BUNDLE,Messages::BUT_SAVE_AS_QUERY)))
				->behaviors(Behaviors::action(
					Generator::url('queries','query',null),
					true,
					'#sSearchRessource'
				));
if(!$screen->item('bAdvanceCriteria')->initial()) {
	$screen->item('bAdvanceCriteria')->collapsed(false);
}
if($screen->submitted() || isset($_POST['keyword']) || $forced)
{
	$inputs = $screen->get();
	
	$data = json_decode(preg_replace("/\'/",'"',$inputs['localization']),true);
	if(is_array($data)) {
		$screen->item('criteria')->item('bAdvanceCriteria')->collapsed(false);
		foreach($data as $item) {
			$id = $item['id'];
			$idValue = substr($id,2);
			switch (substr($id,0,1)) {
				case "r" :
					$regions[] = $idValue;
					break;
				case "g" :
					$groups[] = $idValue;
					break;
				case "s" :
					$subs[] = $idValue;
					break;
			}
		}
		if(isset($groups)) {
			$regionsBuffer = Localization::loadRegionFromGroups($groups);
			$locWishesFragment[] = new SqlFragment("wl.level = ? AND wl.id IN (?)",array(1,$groups),
														array(Type::INTEGER,Connection::PARAM_INT_ARRAY));
			if(isset($regions)) {
				$regions = array_merge($regions,$regionsBuffer);
			} else {
				$regions = $regionsBuffer;
			}
		}
		
		// We need to add the sub filter
		if(isset($subs) || isset($regions)) {
			// Load States Id
			$query = Fox\Dao::infos(SubRegion::className())->handle()->createQueryBuilder();
			$query->select('stateid')->from('region_subs','t');
			if(isset($regions)) {
				$regFragment[] = new SqlFragment("regid IN (?)",array($regions),array(Connection::PARAM_INT_ARRAY));
				$locWishesFragment[] = new SqlFragment("wl.level = ? AND wl.id IN (?)",array(2,$regions),
														array(Type::INTEGER,Connection::PARAM_INT_ARRAY));
			} 
			if(isset($subs)) {
				$regFragment[] = new SqlFragment("regsubid IN (?)",array($subs),array(Connection::PARAM_INT_ARRAY));
				$locWishesFragment[] = new SqlFragment("wl.level = ? AND wl.id IN (?)",array(3,$subs),
														array(Type::INTEGER,Connection::PARAM_INT_ARRAY));
			}
			$regFragment = SqlFragment::combine($regFragment, SqlFragment::MODE_OR);
			$stateids = $regFragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_COLUMN);
			$locWishesFragment[] = new SqlFragment("a.stateid IN (?)",array($stateids),array(Connection::PARAM_INT_ARRAY));
		}
	}
	$interviewed = $wrapper->item("hasbeenintv")->toInt();
	if(!$screen->errors())
	{
		$joins = null;
		$jobInfo = false;

		$filters[] = $wrapper->item('kn_number_year')->fragments();
		//@TODO fix authorization on grouping
		$grouping = $wrapper->item('grouping')->get();
		if(!empty($grouping)) {
			$filters[] = new SqlFragment('grouping = ? OR grouping IS NULL',array($grouping));
		}
		
		if(!empty($inputs['status'])) {
			$filters[] = $wrapper->item('status')->fragments();
		}
		if(!$wrapper->item('kn_number_year')->initial()) {
			
		}
		$handler = Fox\Databases::databases()->handle(Fox\Databases::DB_APP);
		$query = $handler->createQueryBuilder();
		$query->select('DISTINCT t.ressource_id','t.status','t.last_name','t.first_name','t.email','t.kn_number_year',
						'a.phone1','a.phone2','a.city','a.postal_code','a.country',
						'exp.compid','exp.jobtitle','exp.salary','exp.bonus','c.company_name')
						  ->from('ressources','t')
						  ->leftJoin('t','address','a','t.adrid = a.adrid')
						  ->leftJoin('t','ressource_experiences','exp','t.ressource_id = exp.ressource_id AND current = 1')
						  ->leftJoin('t','companies','c','exp.compid = c.compid')
						  ->setMaxResults($inputs['dbcnt']);
		// Manage Interview
		if($interviewed !== null) {
			if($interviewed) {
				$query->innerJoin('t','ressource_interviews','intv','t.ressource_id = intv.ressource_id');
			} else {
				$query->leftJoin('t','ressource_interviews','intv','t.ressource_id = intv.ressource_id');
				$filters[] = new Fox\Dao\SqlFragment('intv.iid IS NULL ');
			}
		}
		
		// Filter on Salary in wishes
		if(!$wrapper->item('targeted_total_salary')->initial())
		{
			$filters[] = $wrapper->item('targeted_total_salary')->fragments();
			$query->innerJoin('t','ressource_desires','des','des.ressource_id = t.ressource_id AND des.current = 1');
		}
		// Filter on whishes localizaton 
		if(isset($locWishesFragment)) {
			$filters[] = SqlFragment::combine($locWishesFragment, SqlFragment::MODE_OR);
			if(!$wrapper->item('targeted_total_salary')->initial()) {
				$query->innerJoin('t','ressource_wish_localizations','wl','des.ressource_id = wl.ressource_id');
			} else {
				$query->leftJoin('t','ressource_desires','des','des.ressource_id = t.ressource_id AND des.current = 1')
						->leftJoin('t','ressource_wish_localizations','wl','des.ressource_id = wl.ressource_id');
			}
		}
		// Experience
		if(!$wrapper->item('available_date')->initial())
		{
			$jobInfo = true;
			$filters[] =  $wrapper->item('available_date')->fragments('end_date');
		} else {
			
		}
		
		try {
			$result = array();
			if(empty($inputs['keyword']))
			{
				$finder = new Hive\Ressource();
				$filters[] = new SqlFragment('t.loekz = ?',array(0));
				$fragment = SqlFragment::combine($filters);
				$handler->beginTransaction();
				$handler->prepare('SET SQL_BIG_SELECTS =1')->execute();
				$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_OBJ); 
				$handler->commit();
			}
			else
			{
				$searchEngine = new SearchEngine();
				$keywords = preg_replace("/\'/",' ',strtolower($inputs['keyword']));
				$matches = $searchEngine->search(explode(' ',$keywords),SearchEngine::MODE_LIKE);
				if(is_array($matches))
				{
					foreach($matches as $match)
					{
						$groupingIds[] = $match['grouping_id'];
					}
					$fragments = $filters;
					$fragments[] = isset($groupingIds) ?  new Fox\Dao\SqlFragment('t.ressource_id IN (?)',
															array($groupingIds),
															array(Doctrine\DBAL\Connection::PARAM_STR_ARRAY)) : null;
					// 
					$fragment = Fox\Dao\SqlFragment::combine($fragments);
					$handler->beginTransaction();
					$handler->prepare('SET SQL_BIG_SELECTS =1')->execute();
					$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_OBJ);
					$handler->commit();
					if(count($result) == 0)
							Fox\Message::Warning(Msg(Fox\Messages\SearchEngine::BUNDLE,Fox\Messages\SearchEngine::NO_DOCUMENT_FOUND));
				}
				else
				{
					Fox\Message::Warning(Msg(Fox\Messages\SearchEngine::BUNDLE,Fox\Messages\SearchEngine::NO_DOCUMENT_FOUND));
				}

			}
			//---- Output View	----
			/* @var $grid Fox\Grid */
			$grid = Xview::instance()->component('ressource','result');
			$grid->title(Msg(Fox::MSGCL,143,count($result)));
			
			$grid->fieldcat()->setGenerators(array(
				'salary' => Fox\Generator::useFunction(function ($data,$key){
					return $data->salary + $data->bonus;
				}),
				'dummy' => Fox\Generator::useFunction(function ($data,$key){
					$country = $data->country;
					if(empty($country) || $country == 'fr')
					{
						$postal_code = substr($data->postal_code,0,2);
						$adress = $data->address()->city;
						$adress .= empty($postal_code) ? null : ' ('.$postal_code.')';
					}
					else
					{
						$adress = \Fox\Ddic\DataElement\Text::label('country',$data->country);
					}
					return $adress;
			
				}),
				'experience' => Fox\Generator::useFunction(function ($data,$key){
					return "<b>{$data->jobtitle}</b> chez {$data->company_name}";
				}),
			));
			
			$grid->initialize($result);
			$grid->setXlsDownload();
			$grid->setColumnManager();
			$grid->layout()->scrollVertical(true);
			// Select to display in the carillon
			$grid->footer()->add(new Button('select',null,Msg(Hive::BUNDLE,Hive::BUT_SHOW)));
			// Change Mission Affectation
			$grid->footer()->add(new Button('assign',null,
					Msg(Hive::BUNDLE,Hive::BUT_ASSIGN_CONTACTS_MISSION)))
						->behaviors(Behaviors::action(
							Generator::url('missions','select',null),
						true,null,null,true,'POST','bd_'.$grid->id()));
			// Change Status
			if(Authorization\Manager::check(Ressource::AUTH_STATUS_CAN_VALID)) {

				$grid->footer()->add(new Button('assign_status',null,
						Msg(Hive::BUNDLE,Hive::BUT_UPDATE_STATUS)))
							->behaviors(Behaviors::action(
								Generator::url('ressource','status',null),
							true,null,null,true,'POST','bd_'.$grid->id()));
			}
			$output = $grid->Display();
		}
		catch (Exception $e)
		{
			Fox\Message::Error($e->getMessage());
		}
	}

}
if(!$ajax)
{
	echo $screen->Display();
	echo '<div id="searchResult">'.$output.'</div>';
}
else
{
	echo Xview::instance()->messages();
	echo $output;
}